﻿CREATE PROCEDURE [dbo].[sp_Get_Leads]
	@AccountID varchar(4), 
	@PeroidMonth varchar(2),
	@PeroidYear varchar(4)
AS
	if @AccountID is not null
	begin
		select l.[LeadID]
		  ,l.[SelPersonID]
		  ,l.[Zip]
		  ,l.[ZipRequesterCode]
		  ,l.[Division]
		  ,l.[Market]
		  ,l.[Territory]
		  ,l.[Zip4]
		  ,l.[CallType]
		  ,l.[LeadType]
		  ,l.[MdNumber]
		  ,l.[SequenceNumber]
		  ,l.[RawData]
		  ,l.[City]
		  ,l.[State]
		  ,l.[County]
		  ,l.[Latitude]
		  ,l.[Longitude], zi.AgentName
		from dbo.Orders o
		inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
		inner join dbo.Zips z on z.ZipID = oz.ZipID
		inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
		inner join dbo.Leads l on l.ZipRequesterCode = z.Zip
		where AccountID = @AccountID and PeroidMonth = @PeroidMonth 
		and PeroidYear = @PeroidYear and IsPurchased = 1
		and ((oz.Type = 1 and l.LeadType <> 'B')
		or (oz.Type = 2 and l.LeadType = 'B')
		or oz.Type = 0)
		order by l.LeadID
	end
	else
	begin
		select l.RawData
		from dbo.Orders o
		inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
		inner join dbo.Zips z on z.ZipID = oz.ZipID
		inner join dbo.Leads l on l.ZipRequesterCode = z.Zip
		where PeroidMonth = @PeroidMonth and PeroidYear = @PeroidYear and IsPurchased = 1
		and ((oz.Type = 1 and l.LeadType <> 'B')
		or (oz.Type = 2 and l.LeadType = 'B')
		or oz.Type = 0)
		order by l.LeadID
	end
